# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

import frappe


def execute(filters=None):
	return get_columns(), get_data(filters)

def get_columns():
	return ["单据号:Link/SRM Sample Request:200", "优先级:50", "项目名称:150","物料名称:150", "规格型号:180", "元件数量:50",
			"内部状态:Link/Workflow State:120", "采购员:100", "需求日期:150", "供应商:150", "预计回样时间_采购:150", 
			"实际回样日期:150", "申请人:50", "用料公司名称:150", "申请日期:150", "邮件发送:50", "样品相关设计文件:150","实际回样数量:50"
			]


def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	priority, workflow_state, creation_from, creation_to = filters.get('priority'), filters.get('workflow_state'), filters.get('creation_from'), filters.get('creation_to')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	if priority:
		conditions += f" AND s1.priority = '{priority}' "
	if workflow_state:
		conditions += f" AND s1.workflow_state = '{workflow_state}' "
	# 把conditions组合进来
	dt = frappe.db.sql(
		f"""select s1.name as '单据号', s1.priority as '优先级', s1.project_name as '项目名称', IFNULL(s1.sample_name,'') as '物料名称', 
			s1.sample_model as '规格型号', s1.element_qty_2 as '元件数量', s1.workflow_state as '内部状态', IFNULL(s1.purchaser_name,'') as '采购员',
			s1.requirement_date as '需求日期', IFNULL(s2.supplier_name, '') as '供应商', IFNULL(s1.answer_date_from_purchase, '') as '预计回样时间_采购',
			IFNULL(s1.real_receive_date, '') as '实际回样日期', s1.applicant_name as '申请人', IFNULL(s1.material_company_name,'') as '用料公司名称',
			s1.creation as '申请日期', IFNULL(s3.status,'') as '邮件发送', IFNULL(s1.sample_file,'') as '样品相关设计文件', IFNULL(s1.real_receive_qty,'') as '实际回样数量' 
			from `tabSRM Sample Request` s1 
			left join `tabSRM Sample Request Sub` s2 on s1.name=s2.parent
			left join `tabEmail Queue` s3 on s1.name=s3.reference_name and s3.status='Sent'
			WHERE s1.creation >= '{creation_from}' and s1.creation <= '{creation_to}' {conditions}
			ORDER BY s1.creation DESC;
		""", as_dict=True
	)
	return dt